﻿Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports DoAnQuanLyBanHang1065043_1065007.DoAnQuanLyBanHang.General

Namespace DoAnQuanLyBanHang.DTO
    Public Class MatHangData

        Private m_MatHangData As New DataService()
        Public Function LayMatHang() As DataTable
            Dim cmd As New SqlCommand("SELECT * FROM Mat_Hang")
            m_MatHangData.Load(cmd)
            Return m_MatHangData
        End Function
        Public Function LayDSMatHang() As DataTable
            Dim cmd As New SqlCommand("SELECT * FROM Mat_Hang MH INNER JOIN Nha_Cung_Cap NCC ON MH.MaNCC = NCC.MaNCC INNER JOIN Loai_Tien LT ON MH.MaLoaiTien = LT.MaLoaiTien INNER JOIN Loai_Giao_Dich LGD ON MH.MaLoaiGiaoDich = LGD.MaLoaiGiaoDich")
            m_MatHangData.Load(cmd)
            Return m_MatHangData
        End Function

        Public Function LayDSMatHangTheoMa(MaPhieu As [String]) As DataTable
            Dim cmd As New SqlCommand("SELECT MH.MaPhieu, MH.MaMH, NCC.MaNCC, NCC.TenNCC, MH.TenMH, LT.LoaiTien, LGD.TenLoaiGiaoDich, MH.SoLuong, MH.GiaBan, MH.DonGia, MH.NgayNhap FROM Mat_Hang MH INNER JOIN Nha_Cung_Cap NCC ON MH.MaNCC = NCC.MaNCC INNER JOIN Loai_Tien LT ON MH.MaLoaiTien = LT.MaLoaiTien INNER JOIN Loai_Giao_Dich LGD ON MH.MaLoaiGiaoDich = LGD.MaLoaiGiaoDich WHERE MH.MaPhieu = @MaPhieu")
            cmd.Parameters.Add("MaPhieu", SqlDbType.VarChar).Value = MaPhieu
            m_MatHangData.Load(cmd)
            Return m_MatHangData
        End Function

        Public Function ThemDongMoi() As DataRow
            Return m_MatHangData.NewRow()
        End Function

        Public Sub ThemMatHang(m_Row As DataRow)
            m_MatHangData.Rows.Add(m_Row)
        End Sub

        Public Function LuuMatHang() As Boolean
            Return m_MatHangData.ExecuteNoneQuery() > 0
        End Function

        Public Sub LuuMatHang(maphieu As [String], mamh As [String], tenmh As [String], soluong As Single, dongia As Single, giaban As Single, _
         mancc As [String], ngaynhap As DateTime, maloaitien As [String], maloaigd As [String])
            Dim cmd As New SqlCommand("INSERT INTO Mat_Hang VALUES(@maphieu, @mamh, @tenmh, @soluong, @dongia, @giaban, @mancc, @ngaynhap, @maloaitien, @maloaigd)")
            cmd.Parameters.Add("maphieu", SqlDbType.VarChar).Value = maphieu
            cmd.Parameters.Add("mamh", SqlDbType.VarChar).Value = mamh
            cmd.Parameters.Add("tenmh", SqlDbType.NVarChar).Value = tenmh
            cmd.Parameters.Add("soluong", SqlDbType.Float).Value = soluong
            cmd.Parameters.Add("dongia", SqlDbType.Float).Value = dongia
            cmd.Parameters.Add("giaban", SqlDbType.Float).Value = giaban
            cmd.Parameters.Add("mancc", SqlDbType.VarChar).Value = mancc
            cmd.Parameters.Add("ngaynhap", SqlDbType.DateTime).Value = ngaynhap
            cmd.Parameters.Add("maloaitien", SqlDbType.VarChar).Value = maloaitien
            cmd.Parameters.Add("maloaigd", SqlDbType.VarChar).Value = maloaigd

            m_MatHangData.Load(cmd)
        End Sub

        Public Sub SuaMatHang(maphieu As [String], mamh As [String], tenmh As [String], soluong As Single, dongia As Single, giaban As Single, _
         mancc As [String], ngaynhap As DateTime, maloaitien As [String], maloaigd As [String])
            Dim cmd As New SqlCommand("UPDATE Mat_Hang SET tenmh= @tenmh, soluong = @soluong, dongia = @dongia, giaban = @giaban, mancc = @mancc, ngaynhap = @ngaynhap, maloaitien = @maloaitien, maloaigiaodich = @maloaigd WHERE mamh = @mamh AND maphieu = @maphieu")
            cmd.Parameters.Add("maphieu", SqlDbType.VarChar).Value = maphieu
            cmd.Parameters.Add("mamh", SqlDbType.VarChar).Value = mamh
            cmd.Parameters.Add("tenmh", SqlDbType.NVarChar).Value = tenmh
            cmd.Parameters.Add("soluong", SqlDbType.Float).Value = soluong
            cmd.Parameters.Add("dongia", SqlDbType.Float).Value = dongia
            cmd.Parameters.Add("giaban", SqlDbType.Float).Value = giaban
            cmd.Parameters.Add("mancc", SqlDbType.VarChar).Value = mancc
            cmd.Parameters.Add("ngaynhap", SqlDbType.DateTime).Value = ngaynhap
            cmd.Parameters.Add("maloaitien", SqlDbType.VarChar).Value = maloaitien
            cmd.Parameters.Add("maloaigd", SqlDbType.VarChar).Value = maloaigd

            m_MatHangData.Load(cmd)
        End Sub

        Public Sub XoaMatHang(maphieu As [String], mamh As [String])
            Dim cmd As New SqlCommand("DELETE FROM Mat_Hang WHERE mamh = @mamh AND maphieu = @maphieu")
            cmd.Parameters.Add("maphieu", SqlDbType.VarChar).Value = maphieu
            cmd.Parameters.Add("mamh", SqlDbType.VarChar).Value = mamh

            m_MatHangData.Load(cmd)
        End Sub

#Region "tim kiem"
        Public Function TimKiemTheoMa(id As [String]) As DataTable
            Dim cmd As New SqlCommand("SELECT * FROM Mat_Hang WHERE MaMH LIKE '%' + @id + '%'")
            cmd.Parameters.Add("id", SqlDbType.VarChar).Value = id

            m_MatHangData.Load(cmd)
            Return m_MatHangData
        End Function

        Public Function TimKiemTheoTen(ten As [String]) As DataTable
            Dim cmd As New SqlCommand("SELECT * FROM Mat_Hang WHERE TenMH LIKE '%' + @ten + '%'")
            cmd.Parameters.Add("ten", SqlDbType.NVarChar).Value = ten

            m_MatHangData.Load(cmd)
            Return m_MatHangData
        End Function



#End Region
    End Class
End Namespace
